package com.my.screenadapter.sql;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Created by ${张英杰} on 2018/6/28.
 */

public class DBUtil extends SQLiteOpenHelper {

    public static final  String DATABASE_NAME = "abc.db";
    public static final int DATABASE_VERSION=2;
    public static final String COMPANY_TABLENAME="company";
    public static final String COMPANY_TABLENAME_TEMP="company_temp";

    public static final String CREATE_TABLE_COMPANY = "CREATE TABLE IF NOT EXISTS "+COMPANY_TABLENAME+
            "(id integer, name text, psersion text)";

    public DBUtil(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    private static final String TAG = "DBUtil";

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "onCreate: ");
        db.execSQL(CREATE_TABLE_COMPANY);
    }

    /**
     * 插入播放计划
     * @return            返回主键id
     */
    public long insertPlan(Integer id,String name,String psersion) {
        Log.i(TAG, "insertPlan: ");
        ContentValues values = new ContentValues();
        values.put("id",id);
        values.put("name", name);
        values.put("psersion", psersion);
        return  getReadableDatabase().insert(COMPANY_TABLENAME, null, values);
    }

    /**
     * 查询当前数据库中Company
     * @return
     */
    public void selectCompany() {
        SQLiteDatabase readableDatabase = getReadableDatabase();
        Log.i(TAG, "selectCompany: ");
        String sql = "select * from "+COMPANY_TABLENAME;
        Cursor cursor = readableDatabase.rawQuery(sql, null);
        if(cursor.getCount()> 0 && cursor.moveToFirst()) {

            int columnCount = cursor.getColumnCount();
            do {
                StringBuffer sb =new StringBuffer();
                for (int i = 0; i <columnCount ; i++) {
                    String columnName = cursor.getColumnName(i);
                    String string = cursor.getString(i);
                    sb.append(columnName+"="+string);
                    sb.append("  , ");
                }
                Log.i(TAG, "selectCompany: "+sb.toString());
            }while (cursor.moveToNext());
        }
    }

    public void execute(String str){
        SQLiteDatabase readableDatabase = getReadableDatabase();
        readableDatabase.execSQL(str);
    }
    private String CREATE_BOOK = "CREATE TABLE IF NOT EXISTS "+COMPANY_TABLENAME+
            "(id integer, name text, psersion text ,temp text)";
    private String CREATE_TEMP_BOOK = "alter table "+COMPANY_TABLENAME+" rename to "+COMPANY_TABLENAME_TEMP;
    private String INSERT_DATA = "insert into "+COMPANY_TABLENAME+" select *,'' from "+COMPANY_TABLENAME_TEMP;
    private String DROP_BOOK = "drop table "+COMPANY_TABLENAME_TEMP;

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "onUpgrade: oldVersion= "+oldVersion+"  newVersion="+newVersion);
        // 使用for实现跨版本升级数据库
        for (int i = oldVersion; i <= newVersion; i++) {
            switch (i) {
                case 2://由 1升到2
                    Log.i(TAG, "onUpgrade: 升级到2 开始");
                    db.execSQL(CREATE_TEMP_BOOK);
                    db.execSQL(CREATE_BOOK);
                    db.execSQL(INSERT_DATA);
                    db.execSQL(DROP_BOOK);
                    Log.i(TAG, "onUpgrade: 升级到2 结束");
                    break;

                default:
                    Log.i(TAG, "onUpgrade default=="+i);
                    break;
            }
        }
    }
}
